Index Object, Indexes Collection Example

This example creates a new Index object, appends it to the Indexes collection of the Employees TableDef, and then enumerates the Indexes collection of the TableDef. Finally, it enumerates a Recordset, first using the primary Index, and then using the new Index. The IndexOutput procedure is required for this procedure to run.

Sub IndexObjectX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim idxNew As Index
    Dim idxLoop As Index
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind!Employees

    With tdfEmployees
        ' Create new index, create and append Field 
        ' objects to its Fields collection.
        Set idxNew = .CreateIndex("NewIndex")

        With idxNew
            .Fields.Append .CreateField("Country")
            .Fields.Append .CreateField("LastName")
            .Fields.Append .CreateField("FirstName")
        End With

        ' Add new Index object to the Indexes collection 
        ' of the Employees table collection.
        .Indexes.Append idxNew
        .Indexes.Refresh

        Debug.Print .Indexes.Count & " Indexes in " & _
            .Name & " TableDef"

        ' Enumerate Indexes collection of Employees 
        ' table.
        For Each idxLoop In .Indexes
            Debug.Print "  " & idxLoop.Name
        Next idxLoop

        Set rstEmployees = _
            dbsNorthwind.OpenRecordset("Employees")

        ' Print report using old and new indexes.
        IndexOutput rstEmployees, "PrimaryKey"
        IndexOutput rstEmployees, idxNew.Name
        rstEmployees.Close

        ' Delete new Index because this is a 
        ' demonstration.
        .Indexes.Delete idxNew.Name
    End With

    dbsNorthwind.Close

End Sub

Sub IndexOutput(rstTemp As Recordset, _
    strIndex As String)
    ' Report function for FieldX.

    With rstTemp
        ' Set the index.
        .Index = strIndex
        .MoveFirst
        Debug.Print "Recordset = " & .Name & _
            ", Index = " & .Index
        Debug.Print "  EmployeeID - Country - Name"

        ' Enumerate the recordset using the specified 
        ' index.
        Do While Not .EOF
            Debug.Print "  " & !EmployeeID & " - " & _
                !Country & " - " & !LastName & ", " & !FirstName
            .MoveNext
        Loop

    End With

End Sub